# -*- coding: utf-8 -*-
from flask import Blueprint, g
from datetime import datetime

from sqlalchemy import func
from common.response import success, error
from common.error_code import ErrorCode
from dao.group_dao import StudyGroup
from filter.auth import auth_required
from filter.decorators import validate_request, require_role
from model.request import CreateExamRequest, ExamScoreRankingRequest, ListExamsRequest, StudentExamResultsRequest, SubmitExamScoreRequest, UpdateExamRequest, UpdateStudentScoreRequest
from dao.db import db
from dao.user_dao import Class, Student, Teacher, User
from dao.exam_dao import Exam, Score
from dao.class_dao import ClassSubjectTeacher

exam_bp = Blueprint('exam', __name__, url_prefix='/api/exam')

@exam_bp.route('/create', methods=['POST'])
@auth_required
@require_role(['admin', 'teacher'])
@validate_request(CreateExamRequest)
def create_exam():
    """创建考试接口（单班/全年级，全年级只存一条记录）"""
    data = g.request_data

    # 1. 验证管理员/教师口令
    current_teacher = Teacher.query.filter_by(user_id=g.user_id).first()
    if not current_teacher:
        return error(ErrorCode.FORBIDDEN, "当前用户不是教师，无法验证口令")
    if current_teacher.password_hash != data.admin_password:
        return error(ErrorCode.INVALID_PARAM, "管理员/教师口令错误")

    # 2. 基础参数校验（class_id 和 grade 二选一）
    if data.class_id is not None and data.grade is not None:
        return error(ErrorCode.INVALID_PARAM, "class_id（单班）和 grade（全年级）不能同时传")
    if data.class_id is None and data.grade is None:
        return error(ErrorCode.INVALID_PARAM, "必须提供 class_id（单班）或 grade（全年级）")

    # 3. 按角色处理不同逻辑
    exam_data = {
        "name": data.name,
        "deadline": data.deadline,
        "created_by": current_teacher.id
    }

    if g.role == 'admin':
        # Admin：支持单班/全年级考试
        if data.grade is not None:
            # 全年级考试：class_id=NULL，校验 grade < 10
            if data.grade >= 10:
                return error(ErrorCode.INVALID_PARAM, "不能给已毕业年级（grade≥10）创建考试")
            # 检查该年级是否存在班级（避免给不存在的年级创建考试）
            if not Class.query.filter_by(grade=data.grade).first():
                return error(ErrorCode.NOT_FOUND, f"年级 {data.grade} 不存在任何班级")
            exam_data["class_id"] = None
            exam_data["grade"] = data.grade
            exam_data["display_grade"] = data.grade
        else:
            # 单班考试：grade=NULL，校验班级存在
            cls = Class.query.get(data.class_id)
            if not cls:
                return error(ErrorCode.NOT_FOUND, f"班级ID={data.class_id} 不存在")
            exam_data["class_id"] = data.class_id
            exam_data["grade"] = None
            exam_data["display_grade"] = None

    else:
        # Teacher：只能创建单班考试（禁止全年级）
        if data.grade is not None:
            return error(ErrorCode.FORBIDDEN, "教师只能创建单班考试，不能创建全年级考试")
        # 校验班级存在 + 教师是该班级的班主任/任课老师
        cls = Class.query.get(data.class_id)
        if not cls:
            return error(ErrorCode.NOT_FOUND, f"班级ID={data.class_id} 不存在")
        is_head_teacher = (cls.head_teacher_id == current_teacher.id)
        is_subject_teacher = ClassSubjectTeacher.query.filter_by(
            class_id=data.class_id,
            teacher_id=current_teacher.id
        ).first()
        if not is_head_teacher and not is_subject_teacher:
            return error(ErrorCode.FORBIDDEN, "你不是该班级的班主任或任课老师，无权创建考试")
        exam_data["class_id"] = data.class_id
        exam_data["grade"] = None
        exam_data["display_grade"] = None

    # 4. 校验截止时间（必须晚于当前时间）
    if data.deadline <= datetime.now():
        return error(ErrorCode.INVALID_PARAM, "截止时间必须晚于当前时间")

    # 5. 检查是否重复创建（避免同一班级/年级重复创建同名考试）
    if Exam.query.filter_by(
        name=data.name,
        class_id=exam_data["class_id"],
        grade=exam_data["grade"]
    ).first():
        return error(ErrorCode.INVALID_PARAM, "该班级/年级已存在同名考试")

    # 6. 创建考试记录（全年级只存一条）
    new_exam = Exam(**exam_data)
    db.session.add(new_exam)
    db.session.commit()

    # 7. 格式化返回结果
    return success({
        "exam_id": new_exam.id,
        "name": new_exam.name,
        "type": "全年级考试" if new_exam.grade is not None else "单班考试",
        "class_id": new_exam.class_id,
        "class_name": new_exam.class_info.name if new_exam.class_id else None,
        "grade": new_exam.grade,
        "deadline": new_exam.deadline.strftime("%Y-%m-%d %H:%M:%S"),
        "created_by": new_exam.created_by,
        "created_at": new_exam.created_at.strftime("%Y-%m-%d %H:%M:%S")
    })

@exam_bp.route('/update', methods=['POST'])
@auth_required
@require_role(['admin', 'teacher'])
@validate_request(UpdateExamRequest)
def update_exam():
    """编辑考试接口（新增deadline和成绩限制）"""
    data = g.request_data

    # 1. 查找考试
    exam = Exam.query.get(data.exam_id)
    if not exam:
        return error(ErrorCode.NOT_FOUND, "考试不存在")

    # 2. 权限检查
    if g.role == 'teacher':
        if exam.class_id is not None:
            cls = Class.query.get(exam.class_id)
            if not cls:
                return error(ErrorCode.NOT_FOUND, "考试对应的班级不存在")
            teacher = Teacher.query.filter_by(user_id=g.user_id).first()
            is_head_teacher = (cls.head_teacher_id == teacher.id)
            is_subject_teacher = ClassSubjectTeacher.query.filter_by(
                class_id=cls.id,
                teacher_id=teacher.id
            ).first()
            if not is_head_teacher and not is_subject_teacher:
                return error(ErrorCode.FORBIDDEN, "你不是该考试所属班级的班主任或任课老师，无权修改")
        else:
            return error(ErrorCode.FORBIDDEN, "教师不能修改全年级考试")

    # 3. 校验 class_id 和 grade 二选一
    if data.class_id is not None and data.grade is not None:
        return error(ErrorCode.INVALID_PARAM, "class_id 和 grade 不能同时传")

    # 4. 检查是否已有成绩记录
    has_scores = Score.query.filter_by(exam_id=exam.id).first() is not None

    # 5. 如果修改 class_id 或 grade
    if data.class_id is not None or data.grade is not None:
        if has_scores and (data.class_id != exam.class_id or data.grade != exam.grade):
            return error(ErrorCode.FORBIDDEN, "该考试已有成绩记录，不能修改班级或年级")

        if data.class_id is not None and data.class_id != exam.class_id:
            cls = Class.query.get(data.class_id)
            if not cls:
                return error(ErrorCode.NOT_FOUND, "目标班级不存在")
            exam.class_id = data.class_id
            exam.grade = None
            exam.display_grade = None
        elif data.grade is not None and data.grade != exam.grade:
            if data.grade >= 10:
                return error(ErrorCode.INVALID_PARAM, "不能给已毕业年级（grade≥10）设置考试")
            if not Class.query.filter_by(grade=data.grade).first():
                return error(ErrorCode.NOT_FOUND, f"年级 {data.grade} 不存在任何班级")
            exam.grade = data.grade
            exam.display_grade = data.grade
            exam.class_id = None

    # 6. 更新名称
    if data.name is not None:
        exam.name = data.name

    # 7. 更新截止时间（增加只能推迟的限制）
    if data.deadline is not None:
        if data.deadline <= datetime.now():
            return error(ErrorCode.INVALID_PARAM, "新的截止时间必须晚于当前时间")

        # 如果原截止时间已过期，则只能推迟
        if exam.deadline < datetime.now() and data.deadline < exam.deadline:
            return error(ErrorCode.FORBIDDEN, "考试截止时间已过，只能推迟不能提前")

        exam.deadline = data.deadline

    # 8. 检查是否重复
    if Exam.query.filter_by(
        name=exam.name,
        class_id=exam.class_id,
        grade=exam.grade
    ).filter(Exam.id != exam.id).first():
        return error(ErrorCode.INVALID_PARAM, "该班级/年级已存在同名考试")

    db.session.commit()

    return success({
        "exam_id": exam.id,
        "name": exam.name,
        "class_id": exam.class_id,
        "grade": exam.grade,
        "deadline": exam.deadline.strftime("%Y-%m-%d %H:%M:%S")
    })

@exam_bp.route('/list', methods=['POST'])
@auth_required
@validate_request(ListExamsRequest)
def list_exams():
    """列出考试列表接口（学生返回考试成绩以及是否已上传成绩）"""
    params = g.request_data

    # 1. 基础查询
    query = Exam.query.order_by(Exam.deadline.asc())

    # 2. 根据角色过滤
    if g.role == 'student':
        # 学生只能查询自己所在班级的考试
        student = Student.query.filter_by(user_id=g.user_id).first()
        if not student:
            return error(ErrorCode.NOT_FOUND, "学生信息不存在")
        student_id = student.id
        query = query.filter(
            (Exam.class_id == student.class_id) |
            (Exam.grade == Class.query.get(student.class_id).grade)
        )

    elif g.role == 'teacher':
        # 教师可以查询自己负责的班级或年级考试
        teacher = Teacher.query.filter_by(user_id=g.user_id).first()
        if not teacher:
            return error(ErrorCode.NOT_FOUND, "教师信息不存在")
        elif params.class_id is not None:
            # 查询自己负责的指定班级考试
            cls = Class.query.get(params.class_id)
            if not cls:
                return error(ErrorCode.NOT_FOUND, "班级不存在")
            is_head_teacher = (cls.head_teacher_id == teacher.id)
            is_subject_teacher = ClassSubjectTeacher.query.filter_by(
                class_id=cls.id,
                teacher_id=teacher.id
            ).first()
            if not is_head_teacher and not is_subject_teacher:
                return error(ErrorCode.FORBIDDEN, "你不是该班级的班主任或任课老师，无权查询")
        else:
            # 默认查询自己负责的所有班级考试
            head_teacher_classes = [c.id for c in Class.query.filter_by(head_teacher_id=teacher.id).all()]
            subject_teacher_classes = [cs.class_id for cs in ClassSubjectTeacher.query.filter_by(teacher_id=teacher.id).all()]
            all_teacher_classes = list(set(head_teacher_classes + subject_teacher_classes))
            query = query.filter(Exam.class_id.in_(all_teacher_classes))

    # admin 无过滤条件

    # 3. 应用时间范围过滤
    if params.start_time is not None:
        query = query.filter(Exam.deadline >= params.start_time)
    if params.end_time is not None:
        query = query.filter(Exam.deadline <= params.end_time)

    # 4. 应用class或grade过滤
    if params.class_id is not None:
        query = query.filter(Exam.class_id == params.class_id)
    if params.grade is not None:
        query = query.filter(Exam.grade == params.grade)

    # 5. 执行查询
    exams = query.all()

    # 6. 格式化返回
    result = []
    for exam in exams:
        class_name = None
        class_grade = None
        if exam.class_id is not None:
            cls = Class.query.get(exam.class_id)
            if cls:
                class_name = cls.name
                class_grade = cls.grade

        item = {
            "exam_id": exam.id,
            "name": exam.name,
            "deadline": exam.deadline.strftime("%Y-%m-%d %H:%M:%S"),
            "class_name": class_name,
            "class_grade": class_grade,
            "class_id": exam.class_id,
            "exam_grade": exam.display_grade
        }

        # 学生角色额外返回考试成绩和是否已上传成绩
        if g.role == 'student':
            score_record = Score.query.filter_by(
                exam_id=exam.id,
                student_id=student_id
            ).first()
            has_uploaded = score_record is not None
            item["has_uploaded"] = has_uploaded
            if has_uploaded:
                item["score"] = float(score_record._score)

        result.append(item)

    return success(result)

@exam_bp.route('/submit-score', methods=['POST'])
@auth_required
@require_role(['student'])
@validate_request(SubmitExamScoreRequest)
def submit_exam_score():
    """学生录入考试成绩接口"""
    data = g.request_data

    # 1. 获取当前学生信息
    student = Student.query.filter_by(user_id=g.user_id).first()
    if not student:
        return error(ErrorCode.NOT_FOUND, "学生信息不存在")

    # 2. 检查考试是否存在
    exam = Exam.query.get(data.exam_id)
    if not exam:
        return error(ErrorCode.NOT_FOUND, "考试不存在")

    # 3. 检查考试是否已截止
    if datetime.now() > exam.deadline:
        return error(ErrorCode.FORBIDDEN, "考试已截止，不能录入成绩, 请联系任课老师补录")

    # 4. 检查考试范围是否匹配学生
    if exam.class_id is not None:
        # 单班考试，必须匹配学生班级
        if student.class_id != exam.class_id:
            return error(ErrorCode.FORBIDDEN, "你不在该考试范围内，不能录入成绩")
    elif exam.grade is not None:
        # 全年级考试，必须匹配学生班级年级
        cls = Class.query.get(student.class_id)
        if not cls or cls.grade != exam.grade:
            return error(ErrorCode.FORBIDDEN, "你不在该考试范围内，不能录入成绩")
    else:
        # 考试没有指定班级或年级（非法数据）
        return error(ErrorCode.INVALID_PARAM, "考试范围未设置，无法录入成绩")

    # 5. 检查成绩范围
    if not (0 <= data.score <= 150):
        return error(ErrorCode.INVALID_PARAM, "成绩必须在 0 到 150 之间")

    # 6. 检查是否已有成绩记录
    score_record = Score.query.filter_by(
        exam_id=data.exam_id,
        student_id=student.id
    ).first()

    if score_record:
        # 更新已有成绩
        score_record.score = data.score
    else:
        # 创建新成绩记录
        score_record = Score(
            exam_id=data.exam_id,
            student_id=student.id,
            score=data.score
        )
        db.session.add(score_record)

    db.session.commit()

    return success({
        "exam_id": exam.id,
        "exam_name": exam.name,
        "student_id": student.id,
        "student_name": student.user.name,
        "score": score_record.score,
        "submitted_at": datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    })

@exam_bp.route('/update-score', methods=['POST'])
@auth_required
@require_role(['admin', 'teacher'])
@validate_request(UpdateStudentScoreRequest)
def update_student_score():
    """教师/管理员更新学生考试分数（支持新增记录）"""
    data = g.request_data

    # 1. 验证管理员/教师身份和口令
    teacher = Teacher.query.filter_by(user_id=g.user_id).first()
    if not teacher:
        return error(ErrorCode.FORBIDDEN, "当前用户不是教师，无权操作")
    
    # 验证口令
    if teacher.password_hash != data.admin_password:
        return error(ErrorCode.INVALID_PARAM, "管理员/教师口令错误")

    # 2. 检查考试是否存在
    exam = Exam.query.get(data.exam_id)
    if not exam:
        return error(ErrorCode.NOT_FOUND, "考试不存在")

    # 3. 检查学生是否存在
    student = Student.query.get(data.student_id)
    if not student:
        return error(ErrorCode.NOT_FOUND, "学生不存在")

    # 4. 检查成绩范围
    if not (0 <= data.new_score <= 150):
        return error(ErrorCode.INVALID_PARAM, "成绩必须在 0 到 150 之间")

    # 5. 权限检查（教师额外限制）
    if g.role == 'teacher':
        # 教师不能修改全年级考试成绩（class_id为null）
        if exam.class_id is None:
            return error(ErrorCode.FORBIDDEN, "教师不能修改全年级考试成绩，只有管理员可以操作")
        
        # 检查教师是否负责该班级
        cls = Class.query.get(exam.class_id)
        if not cls:
            return error(ErrorCode.NOT_FOUND, "考试对应的班级不存在")
            
        is_head_teacher = (cls.head_teacher_id == teacher.id)
        is_subject_teacher = ClassSubjectTeacher.query.filter_by(
            class_id=cls.id,
            teacher_id=teacher.id
        ).first()
        
        if not is_head_teacher and not is_subject_teacher:
            return error(ErrorCode.FORBIDDEN, "你不是该班级的班主任或任课老师，无权修改成绩")

    # 6. 检查学生是否在考试范围内
    if exam.class_id is not None:
        # 单班考试：学生必须属于该班级
        if student.class_id != exam.class_id:
            return error(ErrorCode.FORBIDDEN, "该学生不在此考试范围内")
    elif exam.grade is not None:
        # 全年级考试：学生所在班级必须属于该年级
        cls = Class.query.get(student.class_id)
        if not cls or cls.grade != exam.grade:
            return error(ErrorCode.FORBIDDEN, "该学生不在此考试范围内")

    # 7. 检查是否已有成绩记录（没有则新增）
    score_record = Score.query.filter_by(
        exam_id=data.exam_id,
        student_id=data.student_id
    ).first()

    old_score = None
    if score_record:
        # 更新已有成绩
        old_score = score_record.score
        score_record.score = data.new_score
    else:
        # 新增成绩记录
        score_record = Score(
            exam_id=data.exam_id,
            student_id=data.student_id,
            score=data.new_score
        )
        db.session.add(score_record)

    db.session.commit()

    # 8. 获取更新者姓名
    updater_name = User.query.get(g.user_id).name

    return success({
        "exam_id": exam.id,
        "exam_name": exam.name,
        "student_id": student.id,
        "student_name": student.user.name,
        "old_score": old_score,  # 新增记录时为None
        "new_score": score_record.score,
        "updated_at": datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
        "updated_by": updater_name
    })

@exam_bp.route('/score-ranking', methods=['POST'])
@auth_required
@require_role(['admin', 'teacher'])
@validate_request(ExamScoreRankingRequest)
def exam_score_ranking():
    """统计某几次考试的总分并进行学生排名（处理并列）"""
    params = g.request_data
    
    # 1. 验证考试ID数组不为空
    if not params.exam_ids or len(params.exam_ids) == 0:
        return error(ErrorCode.INVALID_PARAM, "考试ID数组不能为空")
    
    # 2. 获取所有指定的考试并验证
    exams = Exam.query.filter(Exam.id.in_(params.exam_ids)).all()
    if len(exams) != len(params.exam_ids):
        existing_ids = {exam.id for exam in exams}
        invalid_ids = [id for id in params.exam_ids if id not in existing_ids]
        return error(ErrorCode.NOT_FOUND, f"以下考试不存在: {invalid_ids}")
    
    # 3. 验证所有考试的class_id和grade必须一致
    first_exam = exams[0]
    for exam in exams[1:]:
        if exam.class_id != first_exam.class_id or exam.grade != first_exam.grade:
            return error(ErrorCode.INVALID_PARAM, "所有考试的class_id和grade必须一致")
    
    # 4. 验证class_id参数（仅当考试为年级考试时有效）
    if params.class_id is not None:
        if first_exam.class_id is not None:
            return error(ErrorCode.INVALID_PARAM, "class_id参数仅对年级考试有效")
        cls = Class.query.get(params.class_id)
        if not cls:
            return error(ErrorCode.NOT_FOUND, f"班级ID {params.class_id} 不存在")
        if cls.grade != first_exam.grade:
            return error(ErrorCode.INVALID_PARAM, "指定班级的年级与考试年级不一致")
    
    # 5. 权限检查（教师）
    if g.role == 'teacher':
        teacher = Teacher.query.filter_by(user_id=g.user_id).first()
        if not teacher:
            return error(ErrorCode.FORBIDDEN, "当前用户无教师表记录，无权查看排名")
        
        if first_exam.class_id is not None:
            # 班级考试：验证是否为班主任/任课老师
            cls = Class.query.get(first_exam.class_id)
            if not cls:
                return error(ErrorCode.NOT_FOUND, "考试对应的班级不存在")
            is_head_teacher = (cls.head_teacher_id == teacher.id)
            is_subject_teacher = ClassSubjectTeacher.query.filter_by(
                class_id=cls.id, teacher_id=teacher.id
            ).first()
            if not is_head_teacher and not is_subject_teacher:
                return error(ErrorCode.FORBIDDEN, "你不是该班级的班主任或任课老师，无权查看排名")
        else:
            # 年级考试：验证班级权限
            if params.class_id is not None:
                cls = Class.query.get(params.class_id)
                is_head_teacher = (cls.head_teacher_id == teacher.id)
                is_subject_teacher = ClassSubjectTeacher.query.filter_by(
                    class_id=cls.id, teacher_id=teacher.id
                ).first()
                if not is_head_teacher and not is_subject_teacher:
                    return error(ErrorCode.FORBIDDEN, "你不是该班级的班主任或任课老师，无权查看排名")
            else:
                # 验证是否负责该年级至少一个班级
                head_classes = Class.query.filter_by(head_teacher_id=teacher.id, grade=first_exam.grade).all()
                subject_classes = Class.query.join(ClassSubjectTeacher).filter(
                    ClassSubjectTeacher.teacher_id == teacher.id, Class.grade == first_exam.grade
                ).all()
                if not head_classes and not subject_classes:
                    return error(ErrorCode.FORBIDDEN, "你不负责该年级任何班级，无权查看排名")
    
    # 6. 确定需要统计的学生范围
    if first_exam.class_id is not None:
        students_query = Student.query.filter_by(class_id=first_exam.class_id)
    else:
        if params.class_id is not None:
            students_query = Student.query.filter_by(class_id=params.class_id)
        else:
            grade_classes = Class.query.filter_by(grade=first_exam.grade).all()
            class_ids = [cls.id for cls in grade_classes]
            students_query = Student.query.filter(Student.class_id.in_(class_ids))
    
    # 7. 计算学生总分
    students = students_query.all()
    result = []
    for student in students:
        total_score = db.session.query(func.sum(Score._score)).filter(
            Score.student_id == student.id, Score.exam_id.in_(params.exam_ids)
        ).scalar() or 0
        cls = Class.query.get(student.class_id)
        result.append({
            "student_id": student.id,
            "student_name": student.user.name,
            "student_number": student.student_id,
            "class_name": cls.name,
            "class_grade": cls.grade,
            "total_score": round(float(total_score), 2) if total_score else 0.0
        })
    
    # 8. 按总分降序排序 + 计算并列排名
    result.sort(key=lambda x: x["total_score"], reverse=True)
    # 提取所有总分用于排名计算
    all_scores = [item["total_score"] for item in result]
    # 为每个学生添加排名
    for item in result:
        item["rank"] = calculate_parallel_ranking(all_scores, item["total_score"])
    
    return success(result)

@exam_bp.route('/group-score-ranking', methods=['POST'])
@auth_required
@require_role(['admin', 'teacher'])
@validate_request(ExamScoreRankingRequest)
def group_score_ranking():
    """统计某几次考试的总分并进行小组排名（处理并列）"""
    params = g.request_data
    
    # 1. 验证考试ID数组不为空
    if not params.exam_ids or len(params.exam_ids) == 0:
        return error(ErrorCode.INVALID_PARAM, "考试ID数组不能为空")
    
    # 2. 获取所有指定的考试并验证
    exams = Exam.query.filter(Exam.id.in_(params.exam_ids)).all()
    if len(exams) != len(params.exam_ids):
        existing_ids = {exam.id for exam in exams}
        invalid_ids = [id for id in params.exam_ids if id not in existing_ids]
        return error(ErrorCode.NOT_FOUND, f"以下考试不存在: {invalid_ids}")
    
    # 3. 验证所有考试的class_id和grade必须一致
    first_exam = exams[0]
    for exam in exams[1:]:
        if exam.class_id != first_exam.class_id or exam.grade != first_exam.grade:
            return error(ErrorCode.INVALID_PARAM, "所有考试的class_id和grade必须一致")
    
    # 4. 验证class_id参数（仅当考试为年级考试时有效）
    if params.class_id is not None:
        if first_exam.class_id is not None:
            return error(ErrorCode.INVALID_PARAM, "class_id参数仅对年级考试有效")
        cls = Class.query.get(params.class_id)
        if not cls:
            return error(ErrorCode.NOT_FOUND, f"班级ID {params.class_id} 不存在")
        if cls.grade != first_exam.grade:
            return error(ErrorCode.INVALID_PARAM, "指定班级的年级与考试年级不一致")
    
    # 5. 确定参与排名的班级范围
    target_classes = []
    if first_exam.class_id is not None:
        cls = Class.query.get(first_exam.class_id)
        if not cls:
            return error(ErrorCode.NOT_FOUND, "考试对应的班级不存在")
        target_classes = [cls]
    else:
        if params.class_id is not None:
            target_classes = [Class.query.get(params.class_id)]
        else:
            target_classes = Class.query.filter_by(grade=first_exam.grade).all()
    if not target_classes:
        return error(ErrorCode.NOT_FOUND, "未找到符合条件的班级")
    target_class_ids = [cls.id for cls in target_classes]
    
    # 6. 权限检查（教师）
    if g.role == 'teacher':
        teacher = Teacher.query.filter_by(user_id=g.user_id).first()
        if not teacher:
            return error(ErrorCode.NOT_FOUND, "教师信息不存在")
        # 获取教师负责的班级
        head_classes = [c.id for c in Class.query.filter_by(head_teacher_id=teacher.id).all()]
        subject_classes = [cs.class_id for cs in ClassSubjectTeacher.query.filter_by(teacher_id=teacher.id).all()]
        teacher_classes = list(set(head_classes + subject_classes))
        # 验证目标班级权限
        for class_id in target_class_ids:
            if class_id not in teacher_classes:
                return error(ErrorCode.FORBIDDEN, f"你不负责班级ID {class_id}，无权查看该班级小组排名")
    
    # 7. 获取符合条件的小组并计算总分
    groups = StudyGroup.query.filter(StudyGroup.class_id.in_(target_class_ids)).all()
    result = []
    for group in groups:
        group_students = Student.query.filter_by(group_id=group.id).all()
        if not group_students:
            # 小组无学生，总分记为0
            cls = Class.query.get(group.class_id)
            result.append({
                "group_id": group.id,
                "group_name": group.name,
                "total_score": 0.0,
                "average_score": 0.0,
                "class_id": group.class_id,
                "class_name": cls.name if cls else None
            })
            continue

        student_count = len(group_students)
        
        # 计算小组总分
        student_ids = [s.id for s in group_students]
        total_score = db.session.query(func.sum(Score._score)).filter(
            Score.student_id.in_(student_ids), Score.exam_id.in_(params.exam_ids)
        ).scalar() or 0

        # 计算小组平均分
        average_score = round(float(total_score) / student_count, 2) if student_count > 0 else 0.0

        cls = Class.query.get(group.class_id)
        result.append({
            "group_id": group.id,
            "group_name": group.name,
            "total_score": round(float(total_score), 2) if total_score else 0.0,
            "average_score": average_score,
            "class_id": group.class_id,
            "class_name": cls.name if cls else None
        })
    
    # 8. 按平均分降序排序 + 计算并列排名
    result.sort(key=lambda x: x["average_score"], reverse=True)
    # 提取所有小组总分用于排名计算
    all_group_averages = [item["average_score"] for item in result]
    # 为每个小组添加排名
    for item in result:
        item["rank"] = calculate_parallel_ranking(all_group_averages, item["average_score"])
    
    return success(result)

# 通用并列排名计算辅助函数
def calculate_parallel_ranking(all_values, target_value):
    """
    计算目标值在降序排列的列表中的并列排名
    :param all_values: 已降序排序的所有值列表
    :param target_value: 要计算排名的目标值
    :return: 正确的并列排名（int）
    """
    if not all_values:
        return 0
    
    current_rank = 1
    for idx in range(len(all_values)):
        # 当当前值与前一个值不同时，更新排名为当前索引+1
        if idx > 0 and all_values[idx] != all_values[idx-1]:
            current_rank = idx + 1
        # 找到目标值，返回当前排名
        if all_values[idx] == target_value:
            return current_rank
    
    # 理论上不会触发（目标值必在列表中），返回最后一名
    return len(all_values)

@exam_bp.route('/student-results', methods=['POST'])
@auth_required
@require_role(['student'])
@validate_request(StudentExamResultsRequest)
def student_exam_results():
    """学生查看自己在系列考试中的成绩和排名（修正小组排名计算）"""
    params = g.request_data
    
    # 1. 获取当前学生信息
    current_student = Student.query.filter_by(user_id=g.user_id).first()
    if not current_student:
        return error(ErrorCode.NOT_FOUND, "学生信息不存在")
    
    # 获取学生所在班级和小组信息
    student_class = Class.query.get(current_student.class_id)
    if not student_class:
        return error(ErrorCode.NOT_FOUND, "学生所在班级不存在")
    
    student_group = StudyGroup.query.get(current_student.group_id) if current_student.group_id else None
    
    # 2. 验证考试ID数组
    if not params.exam_ids or len(params.exam_ids) == 0:
        return error(ErrorCode.INVALID_PARAM, "考试ID数组不能为空")
    
    # 3. 获取并验证所有考试
    exams = Exam.query.filter(Exam.id.in_(params.exam_ids)).all()
    if len(exams) != len(params.exam_ids):
        existing_ids = {exam.id for exam in exams}
        invalid_ids = [id for id in params.exam_ids if id not in existing_ids]
        return error(ErrorCode.NOT_FOUND, f"以下考试不存在: {invalid_ids}")
    
    # 4. 检查所有考试是否与学生所在班级或年级匹配
    all_grade_exams = True  # 标记是否所有考试都是年级考试
    for exam in exams:
        # 检查考试是否与学生相关
        is_class_match = (exam.class_id is not None and exam.class_id == current_student.class_id)
        is_grade_match = (exam.grade is not None and exam.grade == student_class.grade)
        
        if not is_class_match and not is_grade_match:
            return error(ErrorCode.FORBIDDEN, f"考试ID {exam.id} 与你的班级或年级不匹配，无权查看")
        
        if exam.class_id is not None:
            all_grade_exams = False
    
    # 5. 计算每个考试的成绩和排名
    exam_results = []
    total_score = 0.0
    
    for exam in exams:
        # 获取学生在该考试的成绩
        score_record = Score.query.filter_by(
            exam_id=exam.id,
            student_id=current_student.id
        ).first()
        
        student_score = score_record.score if score_record else None
        if student_score is not None:
            total_score += student_score
        
        # 计算班级排名（个人排名）
        class_rank = calculate_class_rank(exam.id, current_student.class_id, student_score)
        
        # 计算小组排名（基于小组总分）
        group_rank = calculate_group_rank(
            exam.id, 
            current_student.class_id, 
            current_student.group_id
        ) if current_student.group_id else None
        
        # 计算年级排名（仅适用于年级考试）
        grade_rank = None
        if exam.grade is not None:
            grade_rank = calculate_grade_rank(exam.id, exam.grade, student_score)
        
        exam_results.append({
            "exam_id": exam.id,
            "exam_name": exam.name,
            "score": student_score,
            "class_rank": class_rank,  # 个人在班级中的排名
            "group_rank": group_rank,  # 小组在班级中的排名（基于小组总分）
            "grade_rank": grade_rank   # 个人在年级中的排名
        })
    
    # 6. 计算总分及相关排名
    # 个人总分班级排名
    total_class_rank = calculate_total_class_rank(params.exam_ids, current_student.class_id, total_score)
    
    # 小组总分班级排名
    total_group_rank = None
    if current_student.group_id:
        total_group_rank = calculate_total_group_rank(
            params.exam_ids, 
            current_student.class_id, 
            current_student.group_id
        )
    
    # 个人总分年级排名
    total_grade_rank = None
    if all_grade_exams:
        total_grade_rank = calculate_total_grade_rank(params.exam_ids, student_class.grade, total_score)
    
    # 7. 准备返回结果
    return success({
        "student_id": current_student.id,
        "student_name": current_student.user.name,
        "class_id": student_class.id,
        "class_name": student_class.name,
        "group_id": student_group.id if student_group else None,
        "group_name": student_group.name if student_group else None,
        "exam_results": exam_results,
        "total_score": round(total_score, 2) if total_score > 0 else None,
        "total_class_rank": total_class_rank,  # 个人总分班级排名
        "total_group_rank": total_group_rank,  # 小组总分班级排名
        "total_grade_rank": total_grade_rank   # 个人总分年级排名
    })

# 辅助函数：计算个人班级排名
def calculate_class_rank(exam_id, class_id, student_score):
    if student_score is None:
        return None
    
    # 获取班级内所有学生在该考试的成绩（按分数降序）
    class_students = Student.query.filter_by(class_id=class_id).all()
    class_student_ids = [s.id for s in class_students]
    
    # 查询分数并排序
    scores = db.session.query(
        Score.student_id, 
        func.sum(Score._score).label('total')
    ).filter(
        Score.exam_id == exam_id,
        Score.student_id.in_(class_student_ids)
    ).group_by(Score.student_id).order_by(func.sum(Score._score).desc()).all()
    
    # 计算排名
    for rank, (student_id, score) in enumerate(scores, 1):
        if round(float(score), 2) == round(student_score, 2):
            return rank
    return len(scores) + 1

# 辅助函数：计算小组排名（基于小组内所有学生分数总和）
def calculate_group_rank(exam_id, class_id, group_id):
    if group_id is None:
        return None
    
    # 获取班级内所有小组ID
    group_ids = db.session.query(Student.group_id).filter(
        Student.class_id == class_id,
        Student.group_id.isnot(None)
    ).distinct().all()
    group_ids = [g[0] for g in group_ids]
    
    # 计算每个小组的总分
    group_totals = []
    for gid in group_ids:
        # 获取该小组所有学生
        group_students = Student.query.filter_by(
            class_id=class_id,
            group_id=gid
        ).all()
        group_student_ids = [s.id for s in group_students]
        
        # 计算小组总分
        total = db.session.query(func.sum(Score._score)).filter(
            Score.exam_id == exam_id,
            Score.student_id.in_(group_student_ids)
        ).scalar() or 0
        
        group_totals.append((gid, round(float(total), 2)))
    
    # 按小组总分降序排序
    group_totals.sort(key=lambda x: x[1], reverse=True)
    
    # 找到当前小组的排名
    for rank, (gid, total) in enumerate(group_totals, 1):
        if gid == group_id:
            return rank
    return len(group_totals) + 1

# 辅助函数：计算个人年级排名
def calculate_grade_rank(exam_id, grade, student_score):
    if student_score is None:
        return None
    
    # 获取年级内所有班级
    grade_classes = Class.query.filter_by(grade=grade).all()
    class_ids = [cls.id for cls in grade_classes]
    
    # 获取年级内所有学生
    grade_students = Student.query.filter(Student.class_id.in_(class_ids)).all()
    grade_student_ids = [s.id for s in grade_students]
    
    # 查询分数并排序
    scores = db.session.query(
        Score.student_id, 
        func.sum(Score._score).label('total')
    ).filter(
        Score.exam_id == exam_id,
        Score.student_id.in_(grade_student_ids)
    ).group_by(Score.student_id).order_by(func.sum(Score._score).desc()).all()
    
    # 计算排名
    for rank, (student_id, score) in enumerate(scores, 1):
        if round(float(score), 2) == round(student_score, 2):
            return rank
    return len(scores) + 1

# 辅助函数：计算个人总分班级排名
def calculate_total_class_rank(exam_ids, class_id, total_score):
    if total_score is None:
        return None
    
    # 获取班级内所有学生
    class_students = Student.query.filter_by(class_id=class_id).all()
    class_student_ids = [s.id for s in class_students]
    
    # 计算每个学生的总分
    student_totals = []
    for student_id in class_student_ids:
        total = db.session.query(func.sum(Score._score)).filter(
            Score.student_id == student_id,
            Score.exam_id.in_(exam_ids)
        ).scalar() or 0
        student_totals.append((student_id, round(float(total), 2)))
    
    # 按总分排序并计算排名
    student_totals.sort(key=lambda x: x[1], reverse=True)
    
    for rank, (student_id, score) in enumerate(student_totals, 1):
        if score == round(total_score, 2):
            return rank
    return len(student_totals) + 1

# 辅助函数：计算小组总分班级排名
def calculate_total_group_rank(exam_ids, class_id, group_id):
    if group_id is None:
        return None
    
    # 获取班级内所有小组ID
    group_ids = db.session.query(Student.group_id).filter(
        Student.class_id == class_id,
        Student.group_id.isnot(None)
    ).distinct().all()
    group_ids = [g[0] for g in group_ids]
    
    # 计算每个小组的总分
    group_totals = []
    for gid in group_ids:
        # 获取该小组所有学生
        group_students = Student.query.filter_by(
            class_id=class_id,
            group_id=gid
        ).all()
        group_student_ids = [s.id for s in group_students]
        
        # 计算小组在所有考试中的总分
        total = db.session.query(func.sum(Score._score)).filter(
            Score.student_id.in_(group_student_ids),
            Score.exam_id.in_(exam_ids)
        ).scalar() or 0
        
        group_totals.append((gid, round(float(total), 2)))
    
    # 按小组总分降序排序
    group_totals.sort(key=lambda x: x[1], reverse=True)
    
    # 找到当前小组的排名
    for rank, (gid, total) in enumerate(group_totals, 1):
        if gid == group_id:
            return rank
    return len(group_totals) + 1

# 辅助函数：计算个人总分年级排名
def calculate_total_grade_rank(exam_ids, grade, total_score):
    if total_score is None:
        return None
    
    # 获取年级内所有班级和学生
    grade_classes = Class.query.filter_by(grade=grade).all()
    class_ids = [cls.id for cls in grade_classes]
    
    grade_students = Student.query.filter(Student.class_id.in_(class_ids)).all()
    grade_student_ids = [s.id for s in grade_students]
    
    # 计算每个学生的总分
    student_totals = []
    for student_id in grade_student_ids:
        total = db.session.query(func.sum(Score._score)).filter(
            Score.student_id == student_id,
            Score.exam_id.in_(exam_ids)
        ).scalar() or 0
        student_totals.append((student_id, round(float(total), 2)))
    
    # 按总分排序并计算排名
    student_totals.sort(key=lambda x: x[1], reverse=True)
    
    for rank, (student_id, score) in enumerate(student_totals, 1):
        if score == round(total_score, 2):
            return rank
    return len(student_totals) + 1